﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TVDL4.VO;

namespace TVDL4.DAO
{
    public class ThiNghiemDAO
    {
        Random rnd = new Random();
        private dbConnection conn;
        public ThiNghiemDAO()
        {
            conn = new dbConnection();
        }
        /// <summary>
        /// tra ve mot thong tin vat lieu theo ten thi nghiem
        /// </summary>
        /// <param name="tenThiNghiem"></param>
        /// <returns></returns>
        public DataTable SearchByName(string tenThiNghiem)
        {
            string query = string.Format("select * from DMTHINGHIEM a inner join DMVATLIEU b on a.KEYSLVATLIEU = b.KEYSL  where TenThiNghiem like @thinghiem ");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@thinghiem", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(tenThiNghiem);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        /// <summary>
        /// lay tat ca cac du lieu tu table DMTHINGHIEM
        /// </summary>
        /// <returns></returns>
        public DataTable SearchAll()
        {
            string query = string.Format("select * from DMTHINGHIEM a inner join DMVATLIEU b on a.KEYSLVATLIEU = b.KEYSL");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        /// <summary>
        /// insert table DMTHINGHIEM
        /// </summary>
        /// <param name="tenVatLieu"></param>
        /// <returns></returns>
        public ThiNghiemVO InsertRecord(ThiNghiemVO thinghiemVO)
        {
            string query = string.Format("Insert into DMTHINGHIEM(KEYSL,TENTHINGHIEM,KEYSLVATLIEU) VALUES (@keysl,@tenthinghiem,@keyslvatlieu)");
            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(Module.CreateRunninNo(rnd));
            sqlParameters[1] = new SqlParameter("@tenthinghiem", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(thinghiemVO.TenThiNghiem);
            sqlParameters[2] = new SqlParameter("@keyslvatlieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(thinghiemVO.LoaiVatlieu.KeySL);
            if (conn.executeInsertQuery(query, sqlParameters))
            {
                thinghiemVO.KeySL = sqlParameters[0].Value.ToString();
                return thinghiemVO;
            }
            else
            {
                return null;
            }
           
        }
        /// <summary>
        /// Update DMTHINGHIEM
        /// </summary>
        /// <param name="ThiNghiemVO"></param>
        /// <returns></returns>
        public bool UpdateRecord(ThiNghiemVO ThiNghiemVO)
        {
            string query = string.Format("UPDATE DMTHINGHIEM SET TENTHINGHIEM = @thinghiem,KEYSLVATLIEU = @loaivatlieu WHERE KEYSL = @keysl");
            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(ThiNghiemVO.KeySL);
            sqlParameters[1] = new SqlParameter("@thinghiem", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(ThiNghiemVO.TenThiNghiem);
            sqlParameters[2] = new SqlParameter("@loaivatlieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(ThiNghiemVO.LoaiVatlieu.KeySL);
            return conn.executeInsertQuery(query, sqlParameters);
           
        }
        /// <summary>
        /// Xóa DMTHINGHIEM
        /// </summary>
        /// <param name="keySL"></param>
        /// <returns></returns>
        public bool DeleteRecord(string keySL)
        {
            string query = string.Format("Delete DMTHINGHIEM Where KEYSL = @keysl");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(keySL);
            return conn.executeInsertQuery(query, sqlParameters);

        }
        public DataTable SearchThiNghiemByKeySL(string KeySLVatLieu)
        {
            string query = string.Format("select * from DMTHINGHIEM a inner join DMVATLIEU b on a.KEYSLVATLIEU = b.KEYSL where KEYSLVATLIEU = @keyslvatlieu");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keyslvatlieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(KeySLVatLieu);
            return conn.executeSelectQuery(query, sqlParameters);
        }
    }
}
